package com.KMGSoftworks.ShoppingList;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public final class DataHelper {
	   private static final String DATABASE_NAME = "shopping_list.sqlite";
	   private static final int DATABASE_VERSION = 4;
	   private static final String TABLE_NAME = "lists";
	   
	   public static final String KEY_ROWID = "_id";
	   public static final String KEY_TITLE = "name";
	   
	   private Context context;
	   private SQLiteDatabase db;
	   private SQLiteStatement insertListStmt;
	   private static final String INSERT_LIST = "INSERT INTO lists (name) VALUES (?)";

	   private SQLiteStatement insertListItemStmt;
	   private static final String INSERT_LIST_ITEM = "INSERT INTO listitems (listID, itemName, count) VALUES (?,?,?)";
	   
	   private static final String CREATE_LISTS = "CREATE TABLE lists (_id integer primary key autoincrement, name text not null, count integer default 0)";
	   private static final String CREATE_LISTITEMS = "CREATE TABLE listitems (_id integer primary key autoincrement, listID integer not null,  itemName text not null, count integer default 0, " +
	   									"FOREIGN KEY (listID) REFERENCES lists (_id))";
	   
	   public DataHelper(Context context) {
	      this.context = context;
	      OpenHelper openHelper = new OpenHelper(this.context);
	      this.db = openHelper.getWritableDatabase();
	      this.insertListStmt = this.db.compileStatement(INSERT_LIST);
	      this.insertListItemStmt = this.db.compileStatement(INSERT_LIST_ITEM);
	   }
	   
	   public void closeDB(){
		   this.db.close();
	   }
	   
	   public long insertList(String name) {
		   this.insertListStmt.bindString(1, name);
		   return this.insertListStmt.executeInsert();
	   }
	   
	   public void updateList(long rowID, int count){
			ContentValues values = new ContentValues();
			values.put("count", count);
			try{this.db.update("lists", values, "_id=" + rowID, null);}
			catch(Exception e){Log.e("ShoppingLists", e.toString());e.printStackTrace();}
	   }
	   
	   
	   public long insertListItem(int listID, String itemName, int count) {
		   this.insertListItemStmt.bindLong(1, listID);
		   this.insertListItemStmt.bindString(2, itemName);
		   this.insertListItemStmt.bindLong(3, count);
		   return this.insertListItemStmt.executeInsert();
	   }

	   
	   public void deleteAll() {
	      this.db.delete(TABLE_NAME, null, null);
	   }
	   
	   public boolean delete(long rowId) {
	        return this.db.delete(TABLE_NAME, KEY_ROWID + "=" + rowId, null) > 0;
	    }


/*	   
	   public List<String> selectAll() {
	      List<String> list = new ArrayList<String>();
	      Cursor cursor = this.db.query(TABLE_NAME, new String[] { "name" }, null, null, null, null, "name desc");
	      if (cursor.moveToFirst()) {
	         do {
	            list.add(cursor.getString(0));
	         } while (cursor.moveToNext());
	      }
	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }
	      return list;
	   }
*/	   
	   
	   public Cursor selectAll() {
		   	  Log.i("ShoppingList", "SelectAll");
		      return this.db.query(TABLE_NAME, new String[] { "_id","name","count" }, null, null, null, null, "name desc");
		   }

	   
	   private static class OpenHelper extends SQLiteOpenHelper {
	      OpenHelper(Context context) {
	         super(context, DATABASE_NAME, null, DATABASE_VERSION);
	      }
	      @Override
	      public void onCreate(SQLiteDatabase db) {
	    	  Log.i("ShoppingList", "Creating database");
	         db.execSQL(CREATE_LISTS);
	         db.execSQL(CREATE_LISTITEMS);
	      }
	      @Override
	      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	         Log.i("ShoppingList", "Upgrading database, this will drop tables and recreate.");
	         db.execSQL("DROP TABLE IF EXISTS lists");
	         db.execSQL("DROP TABLE IF EXISTS listitems");
	         onCreate(db);
	      }
	   }
	}

